using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using System.Xml.Linq;
using System.IO;
using XLETL.Common;
using XLETL.Model;

namespace XLETL.BLL
{
    	/// <summary>
	/// A business component to manage tasks
	/// </summary>
    public class Task
    {
      Schedule schedule = null;
        /// <summary>
        /// Invoke Template Generator based on task settings stored in task data storage
        /// </summary>
        /// <param name="taskId">TaskId will be included as part of template name</param>
        /// <param name="storagePath">File path where the template will be saved</param>
        public bool GenerateXLTemplate(int taskId, string storagePath)
        {

            return false;
        }

        /// <summary>
        /// Get template file path
        /// </summary>
        /// <param name="taskId"></param>
        public string GetXLTemplatePath(int taskId)
        {

            return "";
        }

        /// <summary>
        /// Save Column Mapping Details for each target column into the system.
        /// </summary>
        /// <param name="targetColumnName"></param>
        /// <param name="sourceColumnName"></param>
        /// <param name="isHidden"></param>
        /// <param name="isKeyColumn"></param>
        /// <param name="sourceColumnType"></param>
        /// <param name="comboBoxDisplay">The parameter include lookup table name,
        /// displaymember name, valuemember name</param>
        /// <param name="checkBoxValue">The parameter value might be ether "bool" or "int"
        /// or null</param>
        /// <param name="taskId"></param>
        public bool SaveColumnMappingDetails(string targetColumnName, string sourceColumnName, bool isHidden, bool isKeyColumn, string sourceColumnType, object comboBoxDisplay, string checkBoxValue, int taskId)
        {

            return false;
        }

        /// <summary>
        /// Save Pre / Post load Sql scripts into task data storage.
        /// </summary>
        /// <param name="taskId"></param>
        /// <param name="preSqlScript"></param>
        /// <param name="postSqlScript"></param>
        public bool SavePrePostSqlScripts(int taskId, string preSqlScript, string postSqlScript)
        {

            return false;
        }

        /// <summary>
        /// Save Wizard Step1 details into task data storage
        /// </summary>
        /// <param name="taskName"></param>
        /// <param name="taskDescription"></param>
        /// <param name="targetTableName"></param>
        /// <param name="targetColumnList">The DataTable contains column list with
        /// attributes (primary key, identity, type, size, etc) selected for ETL
        /// processing</param>
        /// <param name="loadMode"></param>
        public void SaveWizardStep1Details(string taskName, string taskDescription, string targetTableName, DataTable targetColumnList, string loadMode)
        {

        }

        /// <summary>
        /// Delete Task from the system
        /// </summary>
        /// <param name="taskId">Unique identity of task</param>
        public void DeleteTask(Guid taskId)
        {
          if (taskId == null)
          {
            return;
          }

          if (File.Exists(GlobalCache.TASK_STORAGE_PATH))
          {
            // load all users
            XElement existingTasks = XElement.Load(GlobalCache.TASK_STORAGE_PATH);

            //obtain a single company
            IEnumerable<XElement> singleTask = (from targetTask in existingTasks.Elements(TaskInfo.TASK)
                                                where ((Guid)targetTask.Attribute(TaskInfo.TASK_ID)).Equals(taskId)
                                                select targetTask);
            //remove task, should only be 1
            foreach (XElement xe in singleTask)
            {
              // remove template
              
              string templPath = (string)xe.Element(TaskInfo.TEMPLATE_PATH);
              if (!string.IsNullOrEmpty(templPath) && File.Exists(templPath))
              {
                File.Delete(templPath);
              }

              // remove upload folder
              string uplFolder = (string)xe.Element(TaskInfo.FILE_UPLOAD_PATH);
              if (!string.IsNullOrEmpty(uplFolder) && Directory.Exists(uplFolder))
              {
                Directory.Delete(uplFolder, true);
              }

              // remove the task node from xml
              xe.Remove();
            }

            existingTasks.Save(GlobalCache.TASK_STORAGE_PATH);
          }
        }

        /// <summary>
        /// Retrieve Task details specific to Wizard1 screen
        /// </summary>
        /// <param name="taskId">Unique Identity of Task</param>
        public TaskInfo GetTaskDetails(Guid taskID)
        {
            if (schedule == null)
            {
              schedule = new Schedule();
            }

            if (File.Exists(GlobalCache.TASK_STORAGE_PATH))
            {
                XElement existingTasks = XElement.Load(GlobalCache.TASK_STORAGE_PATH);

                var tasks = from taskElement in existingTasks.Elements(TaskInfo.TASK)
                            let tid = (Guid)taskElement.Attribute(TaskInfo.TASK_ID)
                            where tid.Equals(taskID)
                            select new TaskInfo
                            {
                                ID = (Guid)taskElement.Attribute(TaskInfo.TASK_ID),
                                DatabaseID = (Guid)taskElement.Element(TaskInfo.DATABASE_ID),
                                TaskDetails = new TaskDetailsInfo
                                {
                                    Name = (string)taskElement.Element(TaskDetailsInfo.TASK_NAME),
                                    Description = (string)taskElement.Element(TaskDetailsInfo.TASK_DESC),
                                    ProcessMode = (Globals.LoadMode)Enum.Parse(typeof(Globals.LoadMode), (string)taskElement.Element(TaskDetailsInfo.TASK_PROCESS_MODE)),
                                    SourceSheetName = (string)taskElement.Element(TaskDetailsInfo.SOURCE_SHEET_NAME)
                                }
                                ,
                                SourceColumns = (from sourceColElement in taskElement.Descendants(SourceColumnInfo.SOURCE_COLUMN)
                                                 select new SourceColumnInfo
                                                 {
                                                     ID = (Guid)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_ID),
                                                     TargetColumnID = (Guid)sourceColElement.Attribute(SourceColumnInfo.SOURCE_TARGET_COLUMN_ID),
                                                     Name = (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_NAME),
                                                     Style = (Globals.CellStyle)Enum.Parse(typeof(Globals.CellStyle), (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_STYLE)),
                                                     Type = (Globals.ColumnType)Enum.Parse(typeof(Globals.ColumnType), (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_TYPE)),
                                                     NumberFormat = (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_FORMAT),
                                                     IsKeyField = (bool)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_KEY_FIELD),
                                                     Transformations = (from transPlugin in sourceColElement.Descendants(PluginInfo.PLUGIN_INFO)
                                                                        select new PluginInfo
                                                                        {
                                                                            Name = (string)transPlugin.Attribute(PluginInfo.PLUGIN_NAME),
                                                                            Description = (string)transPlugin.Attribute(PluginInfo.PLUGIN_DESC),
                                                                            Type = (string)transPlugin.Attribute(PluginInfo.PLUGIN_TYPE),
                                                                            AssemblyFile = (string)transPlugin.Attribute(PluginInfo.PLUGIN_ASSEMBLY),
                                                                            InstallPath = (string)transPlugin.Attribute(PluginInfo.PLUGIN_INSTALL_PATH),
                                                                            PluginParamList = (from pluginParam in transPlugin.Descendants(PluginInfo.PLUGIN_PARAM)
                                                                                               select new Parameter
                                                                                               {
                                                                                                   ParameterName = (string)pluginParam.Element(PluginInfo.PARAM_NAME),
                                                                                                   ParameterType = (string)pluginParam.Element(PluginInfo.PARAM_TYPE),
                                                                                                   ParameterValue = (string)pluginParam.Element(PluginInfo.PARAM_VALUE)
                                                                                               }).ToArray<Parameter>(),
                                                                            TransformationParamList = (from transParam in sourceColElement.Descendants(PluginInfo.PLUGIN_TRANS_PARAM)
                                                                                                       select new Parameter
                                                                                                       {
                                                                                                           ParameterName = (string)transParam.Element(PluginInfo.PARAM_NAME),
                                                                                                           ParameterType = (string)transParam.Element(PluginInfo.PARAM_TYPE),
                                                                                                           ParameterValue = (string)transParam.Element(PluginInfo.PARAM_VALUE)
                                                                                                       }).ToArray<Parameter>()
                                                                        }).ToList<PluginInfo>()
                                                 }).ToList<SourceColumnInfo>()
                                 ,
                                TargetColumns = (from targetColElement in taskElement.Descendants(TargetColumnInfo.TARGET_COLUMN)
                                                 select new TargetColumnInfo
                                                 {
                                                     ID = (Guid)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_ID),
                                                     Name = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_NAME),
                                                     Type = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_TYPE),
                                                     Size = (int)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_SIZE),
                                                     IsDefaultNull = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_DEFAULT_NULL),
                                                     IsIdentity = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_IDENTITY),                                                     
                                                     IsPrimaryKey = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_PRIMARYKEY),
                                                     IsForeignKey = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_FOREIGNKEY),                                                    
                                                     PrimaryToForeignTableName = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_PR_TO_FR_TABLE_NAME),
                                                     ConstraintName = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_CONSTRAINT_NAME),
                                                     OrderIndex = (int)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_ORDER_INDEX),
                                                     DefaultValue = (object)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_DEFAULT_VALUE),
                                                     Transformations = (from transPlugin in targetColElement.Descendants(PluginInfo.PLUGIN_INFO)
                                                                        select new PluginInfo
                                                                        {
                                                                            Name = (string)transPlugin.Attribute(PluginInfo.PLUGIN_NAME),
                                                                            Description = (string)transPlugin.Attribute(PluginInfo.PLUGIN_DESC),
                                                                            Type = (string)transPlugin.Attribute(PluginInfo.PLUGIN_TYPE),
                                                                            AssemblyFile = (string)transPlugin.Attribute(PluginInfo.PLUGIN_ASSEMBLY),
                                                                            InstallPath = (string)transPlugin.Attribute(PluginInfo.PLUGIN_INSTALL_PATH),
                                                                            PluginParamList = (from pluginParam in transPlugin.Descendants(PluginInfo.PLUGIN_PARAM)
                                                                                               select new Parameter
                                                                                               {
                                                                                                   ParameterName = (string)pluginParam.Element(PluginInfo.PARAM_NAME),
                                                                                                   ParameterType = (string)pluginParam.Element(PluginInfo.PARAM_TYPE),
                                                                                                   ParameterValue = (string)pluginParam.Element(PluginInfo.PARAM_VALUE)
                                                                                               }).ToArray<Parameter>(),
                                                                            TransformationParamList = (from transParam in targetColElement.Descendants(PluginInfo.PLUGIN_TRANS_PARAM)
                                                                                                       select new Parameter
                                                                                                       {
                                                                                                           ParameterName = (string)transParam.Element(PluginInfo.PARAM_NAME),
                                                                                                           ParameterType = (string)transParam.Element(PluginInfo.PARAM_TYPE),
                                                                                                           ParameterValue = (string)transParam.Element(PluginInfo.PARAM_VALUE)
                                                                                                       }).ToArray<Parameter>()
                                                                        }).ToList<PluginInfo>()
                                                 }).ToList<TargetColumnInfo>()
                                ,
                                ColumnMapping = (from mappedColElement in taskElement.Descendants(MappingInfo.COLUMN_MAPPING)
                                                 select new MappingInfo
                                                 {
                                                     SourceColumnID = (Guid)mappedColElement.Attribute(MappingInfo.SOURCE_COLUMN_ID),
                                                     TargetColumnID = (Guid)mappedColElement.Attribute(MappingInfo.TARGET_COLUMN_ID)
                                                 }).ToList<MappingInfo>(),
                                PreLoadSQLScriptID = (string)taskElement.Element(TaskInfo.PRELOAD_SQLSCRIPT),
                                PostLoadSQLScriptID = (string)taskElement.Element(TaskInfo.POSTLOAD_SQLSCRIPT),
                                TargetTableName = (string)taskElement.Element(TaskInfo.TARGET_TABLE_NAME),
                                TemplatePath = (string)taskElement.Element(TaskInfo.TEMPLATE_PATH),
                                TaskSchedule = schedule.GetSchedule((Guid)taskElement.Attribute(TaskInfo.TASK_ID)),
                                FileUploadPath = (string)taskElement.Element(TaskInfo.FILE_UPLOAD_PATH)
                            };

                List<TaskInfo> ls = tasks.ToList<TaskInfo>();
                if (ls.Count > 0)
                {
                    return ls[0];
                }
                else { return null; }
            }
            else return null;
        }

        /// <summary>
        /// Save task status into the system.
        /// </summary>
        /// <param name="taskStatus">Enum object</param>
        /// <param name="taskId">Unique identity of Task</param>
        public void SaveTaskStatus(object taskStatus, int taskId)
        {

        }

        public bool SaveTask(TaskInfo task)
        {
            if (task == null)
            {
                throw new ArgumentNullException("Parameter \"task\" is null");
            }

            Guid newID = Guid.NewGuid();

            // now create upload foler
            task.FileUploadPath = XLETL.Common.GlobalCache.UPLOAD_STORAGE_FOLDER + "\\" + task.DatabaseID.ToString() + "\\" + newID.ToString();

            XElement newTask = new XElement(TaskInfo.TASK, new XAttribute(TaskInfo.TASK_ID, newID),
                    new XElement(TaskDetailsInfo.TASK_NAME, task.TaskDetails.Name),
                    new XElement(TaskDetailsInfo.TASK_DESC, task.TaskDetails.Description),
                    new XElement(TaskDetailsInfo.TASK_PROCESS_MODE, task.TaskDetails.ProcessMode),
                    new XElement(TaskDetailsInfo.SOURCE_SHEET_NAME, task.TaskDetails.SourceSheetName),
                    new XElement(TaskInfo.DATABASE_ID, task.DatabaseID),
                    new XElement(TaskInfo.POSTLOAD_SQLSCRIPT, task.PostLoadSQLScriptID),
                    new XElement(TaskInfo.PRELOAD_SQLSCRIPT, task.PreLoadSQLScriptID),
                    new XElement(TaskInfo.TARGET_TABLE_NAME, task.TargetTableName),
                    new XElement(TaskInfo.TEMPLATE_PATH, Path.Combine(GlobalCache.TEMPLATE_STORAGE_FOLDER, string.Format("{0}.xlsm", newID))),
                    new XElement(TaskInfo.FILE_UPLOAD_PATH, task.FileUploadPath),
                    new XElement(TaskInfo.SOURCE_COLUMNS,
                    from sourceColumn in task.SourceColumns
                    select
                        new XElement(SourceColumnInfo.SOURCE_COLUMN,
                        new XAttribute(SourceColumnInfo.SOURCE_COL_ID, sourceColumn.ID),
                        new XAttribute(SourceColumnInfo.SOURCE_TARGET_COLUMN_ID, sourceColumn.TargetColumnID),
                        new XAttribute(SourceColumnInfo.SOURCE_COL_NAME, sourceColumn.Name),
                        new XAttribute(SourceColumnInfo.SOURCE_COL_TYPE, sourceColumn.Type),
                        new XAttribute(SourceColumnInfo.SOURCE_COL_FORMAT, sourceColumn.NumberFormat),
                        new XAttribute(SourceColumnInfo.SOURCE_COL_STYLE, sourceColumn.Style),
                        new XAttribute(SourceColumnInfo.SOURCE_COL_KEY_FIELD, sourceColumn.IsKeyField),
                        new XElement(SourceColumnInfo.SOURCE_COL_TRANSFORMATIONS,
                            (sourceColumn.Transformations != null) ? 
                            (from transPlugin in sourceColumn.Transformations
                            select
                                new XElement(PluginInfo.PLUGIN_INFO,
                                new XAttribute(PluginInfo.PLUGIN_NAME, transPlugin.Name),
                                new XAttribute(PluginInfo.PLUGIN_DESC, transPlugin.Description),
                                new XAttribute(PluginInfo.PLUGIN_TYPE, transPlugin.Type),
                                new XAttribute(PluginInfo.PLUGIN_ASSEMBLY, transPlugin.AssemblyFile),
                                new XAttribute(PluginInfo.PLUGIN_INSTALL_PATH, transPlugin.InstallPath)
                                ,new XElement(PluginInfo.PLUGIN_PARAMS,
                                (transPlugin.PluginParamList != null) ?
                                (from pluginParam in transPlugin.PluginParamList
                                 select
                                     new XElement(PluginInfo.PLUGIN_PARAM,
                                     new XElement(PluginInfo.PARAM_NAME, pluginParam.ParameterName),
                                     new XElement(PluginInfo.PARAM_TYPE, pluginParam.ParameterType),
                                     new XElement(PluginInfo.PARAM_VALUE, pluginParam.ParameterValue)
                                     )
                                ) : null )
                                ,new XElement(PluginInfo.PLUGIN_TRANS_PARAMS,
                                (transPlugin.TransformationParamList != null) ?
                                (from transformParam in transPlugin.TransformationParamList
                                 select
                                     new XElement(PluginInfo.PLUGIN_TRANS_PARAM,
                                     new XElement(PluginInfo.PARAM_NAME, transformParam.ParameterName),
                                     new XElement(PluginInfo.PARAM_TYPE, transformParam.ParameterType),
                                     new XElement(PluginInfo.PARAM_VALUE, transformParam.ParameterValue)
                                     )
                                ) : null )

                                )
                             ) : null )
                        )
                      ),
                   
                  new XElement(TaskInfo.TARGET_COLUMNS,
                      from targetColumn in task.TargetColumns
                      select
                          new XElement(TargetColumnInfo.TARGET_COLUMN,
                          new XAttribute(TargetColumnInfo.TARGET_COL_ID, targetColumn.ID),
                          new XAttribute(TargetColumnInfo.TARGET_COL_NAME, targetColumn.Name),
                          new XAttribute(TargetColumnInfo.TARGET_COL_TYPE, targetColumn.Type),
                          new XAttribute(TargetColumnInfo.TARGET_COL_SIZE, targetColumn.Size),
                          new XAttribute(TargetColumnInfo.TARGET_COL_DEFAULT_NULL, targetColumn.IsDefaultNull),                          
                          new XAttribute(TargetColumnInfo.TARGET_COL_IDENTITY, targetColumn.IsIdentity),
                          new XAttribute(TargetColumnInfo.TARGET_COL_PRIMARYKEY, targetColumn.IsPrimaryKey),
                          new XAttribute(TargetColumnInfo.TARGET_COL_FOREIGNKEY, targetColumn.IsForeignKey),
                          new XAttribute(TargetColumnInfo.TARGET_COL_PR_TO_FR_TABLE_NAME, (targetColumn.PrimaryToForeignTableName != null) ? targetColumn.PrimaryToForeignTableName : ""),
                          new XAttribute(TargetColumnInfo.TARGET_COL_CONSTRAINT_NAME, (targetColumn.ConstraintName != null) ? targetColumn.ConstraintName : ""),
                          new XAttribute(TargetColumnInfo.TARGET_COL_ORDER_INDEX, targetColumn.OrderIndex),
                          new XAttribute(TargetColumnInfo.TARGET_COL_DEFAULT_VALUE, (targetColumn.DefaultValue != null) ? targetColumn.DefaultValue : ""),
                          new XElement(TargetColumnInfo.TARGET_COL_TRANSFORMATIONS,
                            (targetColumn.Transformations != null) ?
                            (from transPlugin in targetColumn.Transformations
                             select
                                 new XElement(PluginInfo.PLUGIN_INFO,
                                 new XAttribute(PluginInfo.PLUGIN_NAME, transPlugin.Name),
                                 new XAttribute(PluginInfo.PLUGIN_DESC, transPlugin.Description),
                                 new XAttribute(PluginInfo.PLUGIN_TYPE, transPlugin.Type),
                                 new XAttribute(PluginInfo.PLUGIN_ASSEMBLY, transPlugin.AssemblyFile),
                                 new XAttribute(PluginInfo.PLUGIN_INSTALL_PATH, transPlugin.InstallPath)
                                 , new XElement(PluginInfo.PLUGIN_PARAMS,
                                 (transPlugin.PluginParamList != null) ?
                                 (from pluginParam in transPlugin.PluginParamList
                                  select
                                      new XElement(PluginInfo.PLUGIN_PARAM,
                                      new XElement(PluginInfo.PARAM_NAME, pluginParam.ParameterName),
                                      new XElement(PluginInfo.PARAM_TYPE, pluginParam.ParameterType),
                                      new XElement(PluginInfo.PARAM_VALUE, pluginParam.ParameterValue)
                                      )
                                 ) : null)
                                 , new XElement(PluginInfo.PLUGIN_TRANS_PARAMS,
                                 (transPlugin.TransformationParamList != null) ?
                                 (from transformParam in transPlugin.TransformationParamList
                                  select
                                      new XElement(PluginInfo.PLUGIN_TRANS_PARAM,
                                      new XElement(PluginInfo.PARAM_NAME, transformParam.ParameterName),
                                      new XElement(PluginInfo.PARAM_TYPE, transformParam.ParameterType),
                                      new XElement(PluginInfo.PARAM_VALUE, transformParam.ParameterValue)
                                      )
                                 ) : null)

                                 )
                             ) : null)
                          )
                     ),
                 new XElement(TaskInfo.MAPPED_COLUMNS,
                     from mappedColumn in task.ColumnMapping
                     select
                         new XElement(MappingInfo.COLUMN_MAPPING,
                         new XAttribute(MappingInfo.SOURCE_COLUMN_ID, mappedColumn.SourceColumnID),
                         new XAttribute(MappingInfo.TARGET_COLUMN_ID, mappedColumn.TargetColumnID)

                         )
                    )
                );

            XElement tasks = new XElement(TaskInfo.TASKS);

            if (File.Exists(GlobalCache.TASK_STORAGE_PATH))
            {
                XElement existingTasks = XElement.Load(GlobalCache.TASK_STORAGE_PATH);
                tasks.Add(existingTasks.Elements(TaskInfo.TASK));
            }

            tasks.Add(newTask);
            tasks.Save(GlobalCache.TASK_STORAGE_PATH);

            return true;
        }

        /// <summary>
        /// Search for a task based on the database id
        /// </summary>
        public List<TaskInfo> GetTasks(Guid databaseId)
        {
            if (schedule == null)
            {
              schedule = new Schedule();
            }

            if (File.Exists(GlobalCache.TASK_STORAGE_PATH))
            {
                XElement existingTasks = XElement.Load(GlobalCache.TASK_STORAGE_PATH);

                var tasks = from taskElement in existingTasks.Elements(TaskInfo.TASK)
                            let dbid = (Guid)taskElement.Element(TaskInfo.DATABASE_ID)
                            where dbid.Equals(databaseId)
                            select new TaskInfo
                            {
                                ID = (Guid)taskElement.Attribute(TaskInfo.TASK_ID),
                                DatabaseID = (Guid)taskElement.Element(TaskInfo.DATABASE_ID),
                                TaskDetails = new TaskDetailsInfo
                                {
                                    Name = (string)taskElement.Element(TaskDetailsInfo.TASK_NAME),
                                    Description = (string)taskElement.Element(TaskDetailsInfo.TASK_DESC),
                                    ProcessMode = (Globals.LoadMode)Enum.Parse(typeof(Globals.LoadMode), (string)taskElement.Element(TaskDetailsInfo.TASK_PROCESS_MODE)),
                                    SourceSheetName = (string)taskElement.Element(TaskDetailsInfo.SOURCE_SHEET_NAME)
                                }
                                ,SourceColumns = (from sourceColElement in taskElement.Descendants(SourceColumnInfo.SOURCE_COLUMN)
                                                select new SourceColumnInfo
                                                {
                                                    ID = (Guid)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_ID),
                                                    TargetColumnID = (Guid)sourceColElement.Attribute(SourceColumnInfo.SOURCE_TARGET_COLUMN_ID),
                                                    Name = (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_NAME),
                                                    Style = (Globals.CellStyle)Enum.Parse(typeof(Globals.CellStyle), (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_STYLE)),
                                                    Type = (Globals.ColumnType)Enum.Parse(typeof(Globals.ColumnType), (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_TYPE)),
                                                    NumberFormat = (string)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_FORMAT),
                                                    IsKeyField = (bool)sourceColElement.Attribute(SourceColumnInfo.SOURCE_COL_KEY_FIELD),
                                                    Transformations = (from transPlugin in sourceColElement.Descendants(PluginInfo.PLUGIN_INFO)
                                                                       select new PluginInfo
                                                                       {
                                                                           Name = (string)transPlugin.Attribute(PluginInfo.PLUGIN_NAME),
                                                                           Description = (string)transPlugin.Attribute(PluginInfo.PLUGIN_DESC),
                                                                           Type = (string)transPlugin.Attribute(PluginInfo.PLUGIN_TYPE),
                                                                           AssemblyFile = (string)transPlugin.Attribute(PluginInfo.PLUGIN_ASSEMBLY),
                                                                           InstallPath = (string)transPlugin.Attribute(PluginInfo.PLUGIN_INSTALL_PATH),
                                                                           PluginParamList = (from pluginParam in transPlugin.Descendants(PluginInfo.PLUGIN_PARAM)
                                                                                              select new Parameter
                                                                                              {
                                                                                                   ParameterName = (string)pluginParam.Element(PluginInfo.PARAM_NAME),
                                                                                                   ParameterType = (string)pluginParam.Element(PluginInfo.PARAM_TYPE),
                                                                                                   ParameterValue = (string)pluginParam.Element(PluginInfo.PARAM_VALUE)
                                                                                               }).ToArray<Parameter>(),
                                                                           TransformationParamList = (from transParam in sourceColElement.Descendants(PluginInfo.PLUGIN_TRANS_PARAM)
                                                                                   select new Parameter
                                                                                   {
                                                                                       ParameterName = (string)transParam.Element(PluginInfo.PARAM_NAME),
                                                                                       ParameterType = (string)transParam.Element(PluginInfo.PARAM_TYPE),
                                                                                       ParameterValue = (string)transParam.Element(PluginInfo.PARAM_VALUE)
                                                                                   }).ToArray<Parameter>()
                                                                       }).ToList<PluginInfo>()
                                                }).ToList<SourceColumnInfo>()
                                 ,TargetColumns = (from targetColElement in taskElement.Descendants(TargetColumnInfo.TARGET_COLUMN)
                                                 select new TargetColumnInfo
                                                 {
                                                     ID = (Guid)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_ID),
                                                     Name = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_NAME),
                                                     Type = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_TYPE),
                                                     Size = (int)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_SIZE),
                                                     IsDefaultNull = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_DEFAULT_NULL),
                                                     IsIdentity = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_IDENTITY),                                                     
                                                     IsPrimaryKey = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_PRIMARYKEY),
                                                     IsForeignKey = (bool)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_FOREIGNKEY),
                                                     PrimaryToForeignTableName = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_PR_TO_FR_TABLE_NAME),
                                                     ConstraintName = (string)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_CONSTRAINT_NAME),
                                                     OrderIndex = (int)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_ORDER_INDEX),
                                                     DefaultValue = (object)targetColElement.Attribute(TargetColumnInfo.TARGET_COL_DEFAULT_VALUE),
                                                     Transformations = (from transPlugin in targetColElement.Descendants(PluginInfo.PLUGIN_INFO)
                                                                        select new PluginInfo
                                                                        {
                                                                            Name = (string)transPlugin.Attribute(PluginInfo.PLUGIN_NAME),
                                                                            Description = (string)transPlugin.Attribute(PluginInfo.PLUGIN_DESC),
                                                                            Type = (string)transPlugin.Attribute(PluginInfo.PLUGIN_TYPE),
                                                                            AssemblyFile = (string)transPlugin.Attribute(PluginInfo.PLUGIN_ASSEMBLY),
                                                                            InstallPath = (string)transPlugin.Attribute(PluginInfo.PLUGIN_INSTALL_PATH),
                                                                            PluginParamList = (from pluginParam in transPlugin.Descendants(PluginInfo.PLUGIN_PARAM)
                                                                                               select new Parameter
                                                                                               {
                                                                                                   ParameterName = (string)pluginParam.Element(PluginInfo.PARAM_NAME),
                                                                                                   ParameterType = (string)pluginParam.Element(PluginInfo.PARAM_TYPE),
                                                                                                   ParameterValue = (string)pluginParam.Element(PluginInfo.PARAM_VALUE)
                                                                                               }).ToArray<Parameter>(),
                                                                            TransformationParamList = (from transParam in targetColElement.Descendants(PluginInfo.PLUGIN_TRANS_PARAM)
                                                                                                       select new Parameter
                                                                                                       {
                                                                                                           ParameterName = (string)transParam.Element(PluginInfo.PARAM_NAME),
                                                                                                           ParameterType = (string)transParam.Element(PluginInfo.PARAM_TYPE),
                                                                                                           ParameterValue = (string)transParam.Element(PluginInfo.PARAM_VALUE)
                                                                                                       }).ToArray<Parameter>()
                                                                        }).ToList<PluginInfo>()
                                                 }).ToList<TargetColumnInfo>()
                                ,ColumnMapping = (from mappedColElement in taskElement.Descendants(MappingInfo.COLUMN_MAPPING)
                                                select new MappingInfo
                                                {
                                                    SourceColumnID = (Guid)mappedColElement.Attribute(MappingInfo.SOURCE_COLUMN_ID),
                                                    TargetColumnID = (Guid)mappedColElement.Attribute(MappingInfo.TARGET_COLUMN_ID)
                                                }).ToList<MappingInfo>(),                                                
                                PreLoadSQLScriptID = (string)taskElement.Element(TaskInfo.PRELOAD_SQLSCRIPT),
                                PostLoadSQLScriptID = (string)taskElement.Element(TaskInfo.POSTLOAD_SQLSCRIPT),
                                TargetTableName = (string)taskElement.Element(TaskInfo.TARGET_TABLE_NAME),
                                TemplatePath = (string)taskElement.Element(TaskInfo.TEMPLATE_PATH),
                                TaskSchedule = schedule.GetSchedule((Guid)taskElement.Attribute(TaskInfo.TASK_ID)),
                                FileUploadPath = (string)taskElement.Element(TaskInfo.FILE_UPLOAD_PATH)
                            };
                return tasks.ToList<TaskInfo>();
            }
            else return new List<TaskInfo>();
        }
    }
}
